﻿using DevExpress.XtraEditors;
using Lamex.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mCost.Common
{
   public class ExportHelper
    {
        public static bool FindExcelToDataBase(string sourcePath, string targetPath, List<FieldNameInfo> list, string tableName, ref string strError)
        {
            //DataTable dttemp = new DataTable();
            //dttemp.TableName = tableName;
            //foreach (FieldNameInfo info in list)Bill
            //{
            //    dttemp.Columns.Add(info.FieldName);
            //}
            try
            {
                DataTable dttemp = null;
                //DataTable dttemp=Lamex.Common.DataBaseHelper_41.GetDataTableBySql2(" select * from " + tableName + " where 1!=1 ");
                if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                    dttemp = Lamex.Base.BaseBuildSql.GetDataTableBySql(" select * from " + tableName + " where 1!=1 ");
                else
                    dttemp = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(" select * from " + tableName + " where 1!=1 ");

                //DataTable dttemp=Lamex.Common.DataBaseHelper_41.GetDataTableBySql2(" select * from " + tableName + " where 1!=1 ");
                //DataTable dttemp = Lamex.Base.BaseBuildSql.GetDataTableBySql(" select * from " + tableName + " where 1!=1 ");

                //DataTable dttemp=Lamex.Common.DataBaseHelper_41.GetDataTableBySql(" select * from " + tableName + " where 1!=1 ");
                //DataTable dttemp = Lamex.Base.BaseBuildSql.GetDataTableBySql(" select * from " + tableName + " where 1!=1 ");

                FileInfo[] files = new DirectoryInfo(sourcePath).GetFiles().Where(p => (p.Extension.ToLower() == ".xlsx") && p.Extension.ToLower() != ".db").ToArray();
                if(files.Count()==0)
                {
                    if(tableName== "Cost_Labor_Comp_Daily")
                    {
                        strError = "无每日人工成本数据";
                    }
                    if (tableName == "Cost_Social_Insurance_Monthly")
                    {
                        strError = "无社保及公积金数据";
                    }
                }
                if (files.Count() != 0)
                {
                    foreach (FileInfo file in files)
                    {
                        string destName = targetPath + @"\\" + file.Name;
                        if (File.Exists(destName))
                            File.Delete(destName);
                        File.Move(file.FullName, destName);

                        if (file.Name.Contains("~$")) continue;
                        
                        DataTable dt = Lamex.Common.ExecelHelper.ExcelConvertDataTable(0, true, destName);
                        if(dt.Columns.Count==0)
                        {
                            strError = "文档不存在列名";
                            //XtraMessageBox.Show("文档不存在列名");
                            return false;
                        }
                        
                        DataTable dtSave = new DataTable();
                        dt.TableName = tableName;
                        string sql = "insert into " + tableName + "( ";
                        string sql2 = "select ";
                        string field = "";
                        string field2 = "";
                        DataTable dtcol = null;
                        if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                             dtcol = Lamex.Base.BaseBuildSql.GetSysColunmNameList(tableName);
                        else
                             dtcol = Lamex.Common.DataBaseHelper_41.GetSysColunmNameList(tableName);

                        List<string> keyLst = new List<string>();
                        List<string> keyLstStr = new List<string>();
                        bool IsKeyCheck = false;
                        bool IsAutoKey = false;
                        foreach (DataRow dr in dtcol.Rows)
                        {
                            dtcol.TableName = dr["table_name"].ToString();
                            if (dr["keyname"].ToString().ToLower() == "key")
                            {
                                //DataTable IsCheckAuto = Lamex.Common.DataBaseHelper_41.GetDataTableBySql2(@"SELECT * FROM INFORMATION_SCHEMA.columns
                                //        WHERE TABLE_NAME = '" + tableName + @"' AND COLUMNPROPERTY(OBJECT_ID('" + tableName + @"'), COLUMN_NAME, 'IsIdentity') = 1
                                //and COLUMN_NAME = '" + dr["keyname"].ToString().ToLower() + "'")
                                DataTable IsCheckAuto = null;
                                if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                                    IsCheckAuto = Lamex.Base.BaseBuildSql.GetDataTableBySql(@"SELECT * FROM INFORMATION_SCHEMA.columns
                        WHERE TABLE_NAME = '" + tableName + @"' AND COLUMNPROPERTY(OBJECT_ID('" + tableName + @"'), COLUMN_NAME, 'IsIdentity') = 1
                        and COLUMN_NAME = '" + dr["keyname"].ToString().ToLower() + "'");
                               else
                                    IsCheckAuto = Lamex.Common.DataBaseHelper_41.GetDataTableBySql(@"SELECT * FROM INFORMATION_SCHEMA.columns
                        WHERE TABLE_NAME = '" + tableName + @"' AND COLUMNPROPERTY(OBJECT_ID('" + tableName + @"'), COLUMN_NAME, 'IsIdentity') = 1
                        and COLUMN_NAME = '" + dr["keyname"].ToString().ToLower() + "'");

                                if (IsCheckAuto.Rows.Count != 0)
                                {
                                    keyLst.Add(dr["column_name"].ToString().ToLower());
                                    IsAutoKey = true;
                                }
                                keyLstStr.Add(dr["column_name"].ToString().ToLower());
                                IsKeyCheck = true;
                            }
                            int m = 0;
                        }
                        foreach (DataColumn col in dttemp.Columns)
                        {
                            field += col.ColumnName + ",";
                            field2 += col.ColumnName + ",";
                        }
                        field = field.Trim(',');
                        field2 = field2.Trim(',');
                        sql += field;
                        sql2 += field2;

                        sql += " ) ";
                        sql2 += " from #" + tableName + "";
                        sql = sql + sql2;
                        if (string.IsNullOrEmpty(field))
                        {
                            strError = "【数据文档:" + file.FullName + "导入列表名称不规范】";
                            //XtraMessageBox.Show("【数据文档:" + file.FullName + "导入列表名称不规范】");
                            return false;
                        }
                        int iReRow = 0;
                        int iInsRow = 0;
                        int iTotal = 0;
                        List<string> lst = new List<string>();
                        List<string> colNamelist = new List<string>();
                        List<string> removeList = new List<string>();
                        //dt.Columns.Remove("Column1");
                        //check fieldName if match
                        foreach (DataColumn col in dt.Columns)
                        {
                            lst.Add(col.ColumnName);
                        }
                        foreach (string str in lst)
                        {
                            FieldNameInfo strField = list.Where(p => p.CNName== str).FirstOrDefault();
                            if (strField==null)
                                removeList.Add(str);

                        }
                        if (removeList.Count() > 0)
                        {
                            strError = "数据文档:" + file.FullName + "列名或表名不规范！";
                            //XtraMessageBox.Show("数据文档:" + file.FullName + "列名或表名不规范！");
                            return false;
                        }
                        lst.Clear();
                        removeList.Clear();
                        foreach (DataColumn col in dttemp.Columns)
                        {
                            lst.Add(col.ColumnName);

                        }
                        foreach (FieldNameInfo info in list)
                        {
                            string strField = lst.Where(p => p.Contains(info.FieldName)).FirstOrDefault();
                            if (string.IsNullOrEmpty(strField))
                                removeList.Add(info.FieldName);

                        }
                        if (removeList.Count() > 0)
                        {
                            strError = "model列名和表列名不规范！";
                            //XtraMessageBox.Show("model列名和表列名不规范！");
                            return false;
                        }
                        //addtion excel hasn't field make those equal datatable
                        DateTime createDate = file.CreationTime;
                        DateTime modifydDate = file.LastWriteTime;
                        dt.Columns.Add("数据源文件名");
                        dt.Columns.Add("文件创建时间");
                        dt.Columns.Add("文件修改时间");
                        dt.Columns.Add("当前系统时间");
                        dt.Columns.Add("当前MES用户");
                        foreach (DataRow dr in dt.Rows)
                        {
                            dr["数据源文件名"] = file.Name;
                            dr["文件创建时间"] = createDate;
                            dr["文件修改时间"] = modifydDate;
                            dr["当前系统时间"] = DateTime.Now;
                            dr["当前MES用户"] = Lamex.Common.Loginer.CurrentUser.Account;
                        }

                        DataTable dtInset = dttemp.Clone();
                        DataTable dtSuccess = dttemp.Clone();
                        dtInset.Columns.Add("Status");
                        //start check if data have been in database
                        if (!(IsKeyCheck && IsAutoKey))
                        {

                            if (dt.Rows.Count != 0)
                            {

                                foreach (DataRow dr in dt.Rows)
                                {
                                    DataRow dtrow = dtSuccess.NewRow();
                                    string strWhere = "";
                                    DataRow drNew = dtInset.NewRow();
                                    DataRow drInsertRow = dtSuccess.NewRow();
                                    foreach (string s in keyLstStr)
                                    {
                                        foreach (FieldNameInfo info in list)
                                        {
                                            if (s.ToLower() == info.FieldName.ToLower())
                                                strWhere += "and " + s + " = '" + dr[info.CNName] + "'";
                                        }
                                    }
                                    DataTable dtest = null;
                                    if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                                        // DataTable dtest =Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                        dtest = Lamex.Base.BaseBuildSql.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);
                                    else
                                        dtest = Lamex.Common.DataBaseHelper_41.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);
                                    // DataTable dtest =Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                    //                                    DataTable dtest = BaseBuildSql.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);
                                    //=======
                                    //                                    //DataTable dtest =Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                    //                                    DataTable dtest = Lamex.Common.DataBaseHelper_41.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);

                                    if (dtest.Rows.Count > 0)
                                    {
                                        iReRow++;
                                        foreach (DataColumn col in dt.Columns)
                                        {
                                            foreach (FieldNameInfo info in list)
                                            {
                                                if (col.ColumnName.ToLower() == info.CNName.ToLower())
                                                {
                                                    drNew[info.FieldName] = dr[col.ColumnName];
                                                    drInsertRow[info.FieldName] = dr[col.ColumnName];
                                                }
                                            }
                                        }
                                        drNew["Status"] = "数据已存在";
                                        dtSuccess.Rows.Add(drInsertRow);

                                        // Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                        if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                                            Lamex.Base.BaseBuildSql.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                        else
                                            Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);

                                        // Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                        //BaseBuildSql.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);

                                        //Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                        //BaseBuildSql.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);

                                    }
                                    else
                                    {
                                        //foreach (DataColumn col in dt.Columns)
                                        //{
                                        //    foreach (FieldNameInfo info in list)
                                        //    {
                                        //        if (col.ColumnName.ToLower() == info.CNName.ToLower())
                                        //            drNew[info.FieldName] = dr[col.ColumnName];
                                        //    }

                                        //}

                                        //drNew["Status"] = "成功";
                                        foreach (DataColumn col in dt.Columns)
                                        {
                                            foreach (FieldNameInfo info in list)
                                            {
                                                if (col.ColumnName.ToLower() == info.CNName.ToLower())
                                                    drInsertRow[info.FieldName] = dr[col.ColumnName];
                                            }

                                        }
                                        dtSuccess.Rows.Add(drInsertRow);
                                    }

                                    //dtInset.Rows.Add(drNew);
                                }

                                iInsRow = dtInset.Rows.Count;
                                dtSuccess.TableName = tableName;
                                DataSet ds = new DataSet();
                                if (dtSuccess.Rows.Count != 0)
                                {
                                    ds.Tables.Add(dtSuccess);
                                    string errMsg = "";

                                    //Lamex.Common.DataBaseHelper_41.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql);
                                    if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                                    {
                                        if (Lamex.Base.BaseBuildSql.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql, ref errMsg))
                                        {
                                            // XtraMessageBox.Show("成功导入");

                                        }
                                        else
                                        {
                                            XtraMessageBox.Show(errMsg);
                                        }
                                    }
                                    else
                                    {
                                        if (Lamex.Common.DataBaseHelper_41.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql, ref errMsg))
                                        {
                                            // XtraMessageBox.Show("成功导入");

                                        }
                                        else
                                        {
                                            XtraMessageBox.Show(errMsg);
                                        }
                                    }
                                }
                            }
                        }
                        else
                        {
                            foreach (DataRow dr in dt.Rows)
                            {

                                string strWhere = "";
                                DataRow drNew = dtInset.NewRow();
                                DataRow drInsertRow = dtSuccess.NewRow();
                                foreach (string s in keyLst)
                                {
                                    strWhere += "and " + s + " = '" + dr[s] + "'";
                                }
                                //DataTable dtest =Lamex.Common.DataBaseHelper_41.GetDataTableBySql2("Select * from " + tableName + " where 1=1 " + strWhere);
                                DataTable dtest = Lamex.Common.DataBaseHelper_41.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);
                                if (dtest.Rows.Count > 0)
                                {
                                    iReRow++;
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        foreach (FieldNameInfo info in list)
                                        {
                                            if (col.ColumnName.ToLower() == info.CNName.ToLower())
                                            {
                                                drNew[info.FieldName] = dr[col.ColumnName];
                                                drInsertRow[info.FieldName] = dr[col.ColumnName];
                                            }
                                        }
                                    }
                                    drNew["Status"] = "数据已存在";
                                    dtSuccess.Rows.Add(drInsertRow);
                                    Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                }
                                else
                                {
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        drNew[col.ColumnName] = dr[col.ColumnName];
                                    }

                                    //drNew["Status"] = "成功";
                                    drInsertRow.ItemArray = dr.ItemArray;
                                    dtSuccess.Rows.Add(drInsertRow);
                                }

                                dtInset.Rows.Add(drNew);
                            }
                            DataSet ds = new DataSet();
                            if (dtSuccess.Rows.Count != 0)
                            {
                                ds.Tables.Add(dtSuccess);
                                string errMsg = "";
                                //Lamex.Common.DataBaseHelper_41.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql);
                                if (Loginer.CurrentUser.DBName.Contains("测试数据库"))
                                {
                                    if (Lamex.Base.BaseBuildSql.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql, ref errMsg))
                                    {
                                        //XtraMessageBox.Show("成功导入");
                                    }
                                    else
                                    {
                                        XtraMessageBox.Show(errMsg);
                                    }
                                }
                                else
                                {
                                    if (Lamex.Common.DataBaseHelper_41.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql, ref errMsg))
                                    {
                                        //XtraMessageBox.Show("成功导入");
                                    }
                                    else
                                    {
                                        XtraMessageBox.Show(errMsg);
                                    }
                                }
                                
                            }
                        }

                        //if (File.Exists(destName))
                        //    File.Delete(destName);
                        //File.Copy(file.FullName, destName);
                        ////使用File的静态方法删除路径下的一个文件
                        //File.Delete(file.FullName);
                        //string destName = targetPath + @"\\" + file.Name;
                        //File.Move(file.FullName, destName);


                        //string paths = @"D:\MESTemp\";
                        //if (!Directory.Exists(paths))
                        //{
                        //    Directory.CreateDirectory(paths);
                        //}

                        //paths = paths + "MES_TEMP_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";

                        //Lamex.Common.ExecelHelper.ExportDataTableToExcelHeader(dtInset, paths);
                    }
                }
                return true;
            }
            catch(Exception ex)
            {
                strError = ex.Message;
                return false;
            }
            
        }
    }
}
